import { DB } from "https://deno.land/x/sqlite@v3.9.1/mod.ts";
import { existsSync} from "https://deno.land/std/fs/mod.ts";


export async function client(): Promise<DB> {
  const db = new DB("sqlite.db");
  return db;
}


export async function initDb(){
  const initFile =  "./sqlite.db";
  if (!existsSync(initFile)) {
      const db = new DB("sqlite.db");
      console.log("Connected to the SQLite database:", "sqlite.db");
      try {
      
      db.query(`
      CREATE TABLE IF NOT EXISTS raccoon_user(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_name TEXT NOT NULL,
        password TEXT NOT NULL,
        points INTEGER DEFAULT 0,
        raccoon_points INTEGER DEFAULT 0
      )
    `);
      console.log("Table 'raccoon_user' created or already exists.");

      db.query(`
      CREATE TABLE IF NOT EXISTS raccoon_link(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        info TEXT NOT NULL,
        url TEXT NOT NULL,
        user_id INTEGER,
        created_at TEXT,
        status INTEGER,
        add_dtme TEXT,
        positive_points INTEGER DEFAULT 0,
        negative_points INTEGER DEFAULT 0,
        FOREIGN KEY(user_id) REFERENCES raccoon_user(id)
      )
    `);
      console.log("Table 'raccoon_link' created or already exists.");

      db.query(`
      CREATE TABLE IF NOT EXISTS raccoon_link_ratings(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        link_id INTEGER,
        points INTEGER,
        created_at TEXT,
        FOREIGN KEY(user_id) REFERENCES raccoon_user(id),
        FOREIGN KEY(link_id) REFERENCES raccoon_link(id)
      )
    `);
      console.log("Table 'raccoon_link_ratings' created or already exists.");
      db.query(`
      CREATE TABLE IF NOT EXISTS user_ratings(
        user_id INTEGER,
        link_id INTEGER,
        rating_type TEXT,
        FOREIGN KEY(user_id) REFERENCES raccoon_user(id),
        FOREIGN KEY(link_id) REFERENCES raccoon_link(id)
      )
    `);
      console.log("Table 'user_ratings' created or already exists.");
    } catch (error) {
      console.error("Error during database operations:", error);
    }finally{
      closeDB(db)
    }
  }
}

// 关闭数据库连接
export function closeDB(db: DB): void {
  db.close();
  console.log("Database connection closed.");
}
